import os
import re
import tkinter as tk
from tkinter import filedialog
 
# 限定为Oracle SQL语句
def generate_rollback_and_validation_oracle(sql):
   rollback_statements = []
   validation_statements = []
 
   # 分割SQL语句
   statements = sql.split(';')
 
   for statement in statements:
       statement = statement.strip()
       if statement:
           if statement.startswith('CREATE TABLE'):
               # 对于CREATE TABLE，回滚通常是DROP TABLE
               match = re.search(r'CREATE TABLE (\w+\.\w+)', statement)
               if match:
                   table_name = match.group(1)
                   rollback_statements.append(f"DROP TABLE {table_name};")
                   # 验证可以检查表是否存在
                   validation_statements.append(f"SELECT * FROM {table_name} WHERE ROWNUM = 1;")
           elif statement.startswith('DROP TABLE'):
               # 对于DROP TABLE，回滚通常是重新创建表，但这里我们不做处理
               match = re.search(r'DROP TABLE (\w+\.\w+)', statement)
               if match:
                   table_name = match.group(1)
                   rollback_statements.append(f"-- Rollback for DROP TABLE not implemented: {statement}")
                   # 验证可以检查表是否不存在
                   validation_statements.append(f"SELECT * FROM {table_name} WHERE ROWNUM = 1;")
           elif statement.startswith('INSERT'):
                 # 对于INSERT，回滚是DELETE
                match = re.search(r'INSERT INTO (\w+\.\w+) \((.*?)\) VALUES (.*?);', statement)
                if match:
                    table_name = match.group(1)
                    columns = match.group(2).split(',')
                    values = match.group(3).split(',')
                    if values:
                        conditions = [f"{col.strip()} = '{val.strip()}'" for col, val in zip(columns, values)]
                        rollback_statements.append(f"DELETE FROM {table_name} WHERE {' AND '.join(conditions)};")
                        validation_statements.append(f"SELECT * FROM {table_name} WHERE {' AND '.join(conditions)};")
           elif statement.startswith('UPDATE'):
               # 对于UPDATE，回滚是REVERT UPDATE（需要原始值）
               match = re.search(r'UPDATE (\w+\.\w+)', statement)
               if match:
                   table_name = match.group(1)
                   rollback_statements.append(f"-- Rollback for UPDATE not implemented: {statement}")
                   # 验证可以检查更新是否生效
                   where_clause = statement.split('WHERE')[1].strip()
                   validation_statements.append(f"SELECT * FROM {table_name} WHERE {where_clause};")
           elif statement.startswith('DELETE'):
               # 对于DELETE，回滚是重新INSERT
               match = re.search(r'DELETE FROM (\w+\.\w+)', statement)
               if match:
                   table_name = match.group(1)
                   rollback_statements.append(f"-- Rollback for DELETE not implemented: {statement}")
                   # 验证可以检查删除的行是否不存在
                   where_clause = statement.split('WHERE')[1].strip()
                   validation_statements.append(f"SELECT * FROM {table_name} WHERE {where_clause};")
 
   return rollback_statements, validation_statements
 
def get_insert_condition(insert_statement):
    # 获取INSERT语句中的表名和列名
    match = re.search(r'INSERT INTO (\w+\.\w+) \((.*?)\) VALUES', insert_statement)
    if match:
        table_name, columns = match.groups()
        columns = columns.split(',')
        # 对于INSERT语句，我们可以简单地检查插入的值是否在表中已经存在
        # 这里我们获取所有非空值作为条件
        conditions = []
        for col in columns:
            if col.strip():
                value = insert_statement.split(',')[columns.index(col)].strip()
                condition = f"{col.strip()} = '{value}'"
                conditions.append(condition)
        return ' AND '.join(conditions)


 
def read_sql_file(file_path):
   with open(file_path, 'r') as file:
       return file.read()
 
def write_sql_file(file_path, statements):
   with open(file_path, 'w') as file:
       file.write('\n'.join(statements))
 
def choose_sql_file():
  root = tk.Tk()
  root.withdraw()  # 隐藏主窗口
 
  # 弹出文件选择窗口
  file_path = filedialog.askopenfilename(
      title="Select SQL File",
      filetypes=(("SQL files", "*.sql"), ("All files", "*.*"))
  )
 
  return file_path
 
def main():
  # 选择SQL文件
  sql_file_path = choose_sql_file()
 
  if sql_file_path:
      # 读取SQL文件
      sql_content = read_sql_file(sql_file_path)
 
      # 生成回滚和验证语句
      rollback_statements, validation_statements = generate_rollback_and_validation_oracle(sql_content)
 
      # 写入回滚文件
      rollback_file_path = 'rollback.sql'
      write_sql_file(rollback_file_path, rollback_statements)
 
      # 写入验证文件
      validation_file_path = 'validation.sql'
      write_sql_file(validation_file_path, validation_statements)
 
      print(f"Rollback statements written to {rollback_file_path}")
      print(f"Validation statements written to {validation_file_path}")
 
if __name__ == '__main__':
  main()